--------------------------
-- entity change
--------------------------
CREATE TABLE "change"
(
    "id"           int8         NOT NULL,
    "domain"       varchar(100) NOT NULL,
    "date_logged"  timestamp(6) NOT NULL,
    "method"       int2         NOT NULL,
    "service_id"   varchar(100) NOT NULL,
    "after"        jsonb,
    "before"       jsonb,
    "date_created" timestamp(6),
    PRIMARY KEY ("id", "date_logged")
);
--------------------------
-- timescaledb hypertable change
--------------------------
DO
$$
    BEGIN
        IF
            EXISTS(SELECT * FROM pg_available_extensions WHERE "name" = 'timescaledb') THEN
            PERFORM create_hypertable('change', 'date_logged');
        END IF;
    END;
$$
LANGUAGE plpgsql;
--------------------------
-- entity change index
--------------------------
CREATE
    INDEX ON "change" ("domain", "date_logged", "method");

--------------------------
-- entity exception
--------------------------
CREATE TABLE "exception"
(
    "id"           int8         NOT NULL,
    "service_id"   varchar(100) NOT NULL,
    "process_id"   varchar(100) NOT NULL,
    "date_logged"  timestamp(6) NOT NULL,
    "type"         varchar(200) NOT NULL,
    "stack_trace"  text         NOT NULL,
    "date_created" timestamp(6),
    PRIMARY KEY ("id", "date_logged")
);
--------------------------
-- timescaledb hypertable exception
--------------------------
DO
$$
    BEGIN
        IF
            EXISTS(SELECT * FROM pg_available_extensions WHERE "name" = 'timescaledb') THEN
            PERFORM create_hypertable('exception', 'date_logged');
        END IF;
    END;
$$
LANGUAGE plpgsql;
--------------------------
-- entity exception index
--------------------------
CREATE
    INDEX ON "exception" ("service_id", "date_logged", "type");

--------------------------
-- entity identity
--------------------------
CREATE TABLE "auth"
(
    "id"           int8         NOT NULL,
    "username"     varchar(100) NOT NULL,
    "date_logged"  timestamp(6) NOT NULL,
    "action"       int2         NOT NULL,
    "additions"    jsonb,
    "date_created" timestamp(6),
    PRIMARY KEY ("id", "date_logged")
);
--------------------------
-- timescaledb hypertable authorisation
--------------------------
DO
$$
    BEGIN
        IF
            EXISTS(SELECT * FROM pg_available_extensions WHERE "name" = 'timescaledb') THEN
            PERFORM create_hypertable('auth', 'date_logged');
        END IF;
    END;
$$
LANGUAGE plpgsql;
--------------------------
-- entity authorisation index
--------------------------
CREATE
    INDEX ON "auth" ("username", "date_logged", "action");

--------------------------
-- entity note
--------------------------
CREATE TABLE "note"
(
    "id"           int8         NOT NULL,
    "date_logged"  timestamp(6) NOT NULL,
    "service_id"   varchar(100) NOT NULL,
    "process_id"   varchar(100) NOT NULL,
    "level"        int2         NOT NULL,
    "message"      jsonb        NOT NULL,
    "date_created" timestamp(6),
    PRIMARY KEY ("id", "date_logged")
);
--------------------------
-- timescaledb hypertable journal
--------------------------
DO
$$
    BEGIN
        IF
            EXISTS(SELECT * FROM pg_available_extensions WHERE "name" = 'timescaledb') THEN
            PERFORM create_hypertable('note', 'date_logged');
        END IF;
    END;
$$
LANGUAGE plpgsql;
--------------------------
-- entity note index
--------------------------
CREATE
    INDEX ON "note" ("service_id", "level", "date_logged");
CREATE
    INDEX ON "note" USING GIN ("message");

--------------------------
-- entity operation
--------------------------
CREATE TABLE "operation"
(
    "id"           int8         NOT NULL,
    "operator"     varchar(100) NOT NULL,
    "date_logged"  timestamp(6) NOT NULL,
    "endpoint"     varchar(100) NOT NULL,
    "function"     varchar(100) NOT NULL,
    "command"      varchar(100) NOT NULL,
    "arguments"    jsonb,
    "service_id"   varchar(100) NOT NULL,
    "date_created" timestamp(6),
    PRIMARY KEY ("id", "date_logged")
);
--------------------------
-- timescaledb hypertable operation
--------------------------
DO
$$
    BEGIN
        IF
            EXISTS(SELECT * FROM pg_available_extensions WHERE "name" = 'timescaledb') THEN
            PERFORM create_hypertable('operation', 'date_logged');
        END IF;
    END;
$$
LANGUAGE plpgsql;

--------------------------
-- entity operation index
--------------------------
CREATE
    INDEX ON "operation" ("endpoint", "operator", "date_logged", "function", "command", "service_id");